Skip Headers

Oracle® Access Manager for AS/400 Installation and User's Guide 10g Release 2 (10.2) for IBM iSeries OS/400 Part Number B16223-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 7 Distributed Transaction Coordination

Read this chapter to learn about two-phase commit processing. It includes the following sections:

- Two-Phase Commit Overview

- Two-Phase Commit and Oracle Access Manager for AS/400

- Two-Phase Commit Process

- In-Doubt Transactions

- Enabling Two-Phase Commit Processing

- Oracle Required Privileges

- AM400_COM Program

## 7.1 Two-Phase Commit Overview

Oracle Access Manager for AS/400 supports two-phase commit processing to ensure transaction consistency in a heterogeneous environment. Two-phase commit processing is an optional feature in Oracle Access Manager.

During installation, the Access Manager parameter for two-phase commit processing defaults to `*NO`. To enable transaction consistency in a heterogeneous environment, the two-phase commit parameter must be changed to `*YES` on the Change Access Manager Default Options panel or by using the `CHGSQLDFLT` command. For additional information, refer to Chapter 6, "Configuring Access Manager".

Note: If the two-phase commit parameter is set to `*NO,` then data might not be synchronized between local and remote databases. Synchronization is needed only when changes in data (changes in files or tables) are being made in a transaction both on the AS/400 and on the remote Oracle server.

### 7.1.1 Terminology

AM400_RID program automatically resolves in-doubt transactions. This program must be active to enable two-phase commit processing. The AM400_RID program runs in a subsystem which has the same name as the install library.

AM400_COM program provides a mechanism for privileged users to dynamically communicate with the AM400_RID program.

Two-phase commit parameter enables two-phase commit processing in Oracle Access Manager for AS/400. The default setting is `*NO`. It must be changed to `*YES` to enable two-phase commit processing.

Commit point site is the processing node that initiates a commit or roll back. The AS/400 that is participating in a two-phase commit transaction is always designated as the commit point site. The commit point site is also known as the "coordinator" site.

Prepare phase is the phase during which the coordinator asks participants to prepare to commit or to roll back the changes that were made during a transaction.

Commit phase is the phase during which the coordinator asks all nodes to commit the transactions, if all participants have responded to the coordinator that they are prepared to do so. If any participants cannot prepare, then the coordinator asks all nodes to roll back the transaction.

Implied commit When a program (activation group) ends without error, default behavior for the AS/400 system triggers all pending updates to be committed. This commitment of all pending updates would include updates to Oracle data as part of the AS/400 transactions through the Oracle Access Manager for AS/400.

## 7.2 Two-Phase Commit and Oracle Access Manager for AS/400

Oracle Access Manager for AS/400 enables AS/400 applications to update data on both the AS/400 and remote Oracle servers during a single transaction. Any time that updates are allowed in a distributed environment, a new level of complexity is introduced. When multiple users are sharing and accessing data that exists at many sites, rather than at a single site, these distributed transactions require careful management.

With Oracle Access Manager for AS/400, you now have the option to ensure that all participants (nodes) in a distributed transaction react unanimously to the action to take when a transaction end. That is, they all commit, or they all roll back.

For a database to fully participate in a two-phase commit transaction, it must support both a prepare phase and a commit phase for communicating transactions. The prepare phase ensures that all participating nodes that are referenced in a distributed transaction are prepared to commit or to abort the transaction, regardless of intervening failures. The prepare phase is performed when a `COMMIT` command is issued at the end of a distributed transaction. The commit phase is performed when all transaction participants have responded to the coordinator that they are prepared to commit.

The Oracle server supports two-phase commit transactions. Therefore, any number of Oracle servers can participate in a distributed two-phase commit transaction.

Note: If the two-phase commit parameter has been set to `*YES,` and if the AM400_RID program is active, then the prepare phase occurs automatically when an application `COMMIT` is executed. No other action is necessary.

## 7.3 Two-Phase Commit Process

There are two phases for two-phase commit processing:

1. prepare phase

2. commit phase

### 7.3.1 Phase 1: Prepare

When DB2/400 receives a commit request from an application or from STRSQL, it asks all participants in the distributed system to enter a prepared state (to promise to commit or roll back the transaction, even if there is a failure). The participating sites reply either that they are prepared to commit or that they are not prepared to commit. When Access Manager receives such a commit request from DB2/400, it broadcasts an informational message to the AM400_RID program. This communication is one way. The AM400_RID program sends no communication to the Access Manager.

### 7.3.2 Phase 2: Commit

If the AS/400 receives a `prepared to commit` message from all the participating sites, then the AS/400 broadcasts a `COMMIT` command to all sites. If even one site has replied that it is not prepared to commit, then the AS/400 aborts the transaction and broadcasts a `ROLLBACK` command. When an Access Manager site has committed, an information message is sent to the AM400_RID program to announce the end of the transaction.

Note: Access Manager operates under AS/400 commitment control, including implied commits (defined in section "Implied commit"). Refer to the appropriate IBM AS/400 documentation for additional information.

## 7.4 In-Doubt Transactions

An in-doubt transaction occurs when a program or operation terminates abnormally before committing or rolling back the transaction. This creates an outstanding transaction, which must be resolved in order to free the involved resources and to ensure transaction consistency.

### 7.4.1 Resolving In-Doubt Transactions

In-doubt transactions can be resolved automatically by Oracle Access Manager for AS/400. Oracle Access Manager uses a separate task to resolve in-doubt transactions. This task runs in its own subsystem and must be active to enable two-phase commit processing. The task runs the AM400_RID program. On the `WRKACTJOB` display, the AM400_RIDI job will be seen to be active in the "lib" system (where "lib" is the name of the install library).

The AM400_RID program controls the automatic resolve-in-doubt processing for Oracle Access Manager for AS/400. This program runs in its own subsystem parallel to user programs. It is not called by user programs. The ability to perform resolve-in-doubt processing is required if two-phase commit was requested on the `CHGSQLDFLT` command through the "Access Manager Two-Phase Commit" line by specifying `*YES` to that option. The AM400_RID program performs resolve-in-doubt processing only for those Oracle Servers to which it has a valid connection. The only Oracle Servers to which the AM400_RID program might have a valid connection are those named in the list of Oracle Servers on the first page of the `CHG2PCPRM` command (the AM400_COM, see later, can actually be used to dynamically add Oracle Servers to that list). When a user attempts to `CONNECT`, the Access Manager queries the status of the AM400_RID program to see if it has a valid connection to the same Oracle Server to which the user is attempting to `CONNECT`. If it does, then the user connection is allowed. If it does not have a valid connection, then the user `CONNECT` fails.

Note: Resolve-in-doubt processing actually occurs very rarely. It usually occurs due to some catastrophic failure. But it is required in order to keep data in a consistent state among the systems performing a distributed transaction. Informational messages are sent from the Access Manager program to the AM400_RID program every time a transaction changes status. No user data is sent in these messages. The Oracle user ID specified for each connection that AM400_RID has with an Oracle Server must have rather high privileges. User ID (and password) are specified on the second screen of the `CHG2PCPRM` command when you are adding or modifying a possible connection. See Section 7.6, Oracle required privileges for further information.

Figure 7-1 AS/400 In-Doubt Transactions

As a transaction progresses, the Access Manager sends status messages to the AM400_RID program. The AM400_RID program does not send anything back to the Access Manager. In this manner, the AM400_RID program is kept informed of the status of all Oracle Access Manager transactions.

## 7.5 Enabling Two-Phase Commit Processing

To enable two phase commit processing, perform the following steps.

### 7.5.1 Checklist for Enabling Two-Phase Commit

- Step 1: Change Access Manager Two-Phase Commit Default

- Step 2: Change the AM400_RID Options

- Step 3: Add or Remove AM400_COM Privileged User IDs

- Step 4: Start the Subsystem that Runs the AM400_RID Program

- Step 5: Start the AM400_COM Program (Optional)

### 7.5.2 Step 1: Change Access Manager Two-Phase Commit Default

Change the Access Manager two-phase commit parameter to `*YES`. For more information about this panel and its settings, refer to Chapter 6, "Configuring Access Manager".

### 7.5.3 Step 2: Change the AM400_RID Options

After issuing the `CHG2PCPRM` command, the first panel that is displayed confirms which library is to be changed and confirms the four resolve-in-doubt (RID) single-valued options that can be manipulated. The library name should match the library where you installed the Oracle Access Manager for AS/400. These four single-valued options are listed in the panel as: "DataQueue Timeout (in seconds)", "CleanUp Delay (in seconds)", "Trace Level", and "Dead Check counter". After these single-valued options, space is available on the panel to describe the Oracle servers to which Oracle Access Manager for AS/400 will connect in response to a connection request in STRSQL or in user applications.

Example 7-1 Change Resolve In Doubt (RID) Options Panel

    _______________________________________________________________________________________
                             Change RID (Resolve In Doubt) Options
                                                                           System: HQAS400
     Installation Library:  ORAAM130

     DataQueue Timeout (in seconds)  . . . . . .  90
     CleanUp Delay (in seconds)  . . . . . . . .  30
     Trace Level . . . . . . . . . . . . . . . .  49
     Dead Check Counter  . . . . . . . . . . . . 100

     Type options, press Enter.
      1=Add  2=Change  4=Remove

     Opt      Oracle Server Name       Description
              VMS_SRV1                 Connect to VMS1X system
              MVS_920                  MVS running Oracle 9.2.0
              MVS_901                  MVS running Oracle 9.0.1
              PC_GTW_92                WinNt 4.0 server - 9.2
              H920_130                 SUN running Oracle 9.2.0
                                                                           Bottom

     F1=Help  F4=Prompt  F9=Retrieve  F10=Switch to PrivID screen  F12=Cancel

     (C) Copyright Oracle Corporation, 2006
    _______________________________________________________________________________________

Caution: The `CLEANUP_DELAY`, `DATAQUEUE_TIMEOUT`, and `DEAD_CHECK_COUNTER` are tuning parameters that are used to govern how often and when the AM400_RID program attempts to perform resolve in-doubt tasks. Do not change these parameters unless directed to do so by Oracle Support Services.

`Oracle Server Name` defines the name of any Oracle server that is to be accessed by Access Manager. If a server is not found in this list of servers, then it cannot be accessed by Access Manager when two-phase commit is enabled. When resolving an in-doubt transaction, the AM400_RID program uses this list to identify the Oracle server to which it must communicate. The Oracle server name is the same as the AS/400 RDB directory name and the TNS entry name.

The tuning parameters are described below:

`DATAQUEUE_TIMEOUT` is a tuning parameter that requires a single numeric value (in seconds). With two-phase commit enabled, every Oracle Access Manager user task transmits a status signal to the AM400_RID program for every connect and disconnect and at the beginning and end of every transaction. If a status signal is not received by the AM400_RID program after a specified interval, then a timeout occurs.

The AM400_RID program will check the status of all jobs (about which it knows) at least one time in every `n` seconds, where `n` is specified by the `DATAQUEUE_TIMEOUT` parameter. If a connection has already been terminated when the timeout occurs, then the AM400_RID program attempts to perform resolve in-doubt tasks.

`CLEANUP_DELAY` is a tuning parameter that requires a single numeric value (in seconds). It specifies the amount of time that the AM400_RID program waits (after it has determined that resolve-in-doubt processing might be needed) before attempting to perform resolve in-doubt tasks.

`TRACE_LEVEL` determines the trace level for the AM400_RID program. You should change this parameter only when requested to do so by Oracle Support Services.

`DEAD_CHECK_COUNTER` is a tuning parameter that requires a single numeric value. It specifies the minimum (or threshold) number of status signals that will be received by the AM400_RID program before it attempts to perform resolve in-doubt tasks. If the user connection still exists, then no action is taken.

Note: The `DATAQUEUE_TIMEOUT` and `DEAD_CHECK_COUNTER` together determine how often the AM400_RID program checks to see if an in-doubt transaction might be present. `DATAQUEUE_TIMEOUT` specifies a value in `time space`, and `DEAD_CHECK_COUNTER` specifies a value in `event-count space`.

To add a new Oracle server, select option 1. To change the attributes of an existing Oracle server, select option 2. After selecting either option, the following panel is displayed:

Example 7-2 Change or Enter New Server Attributes Panel

    _______________________________________________________________________________________
                              Change or Enter New Server Attributes
                                                                           System: HQAS400
     Installation Library  . . . . . . . .  ORAAM130
     Oracle Server Name  . . . . . . . . .  VMS_SRV1
     Privileged Oracle Id  . . . . . . . .  VMS1Z
     Password for Oracle Id  . . . . . . .  zilchnut
     Startup Parameter . . . . . . . . . .  startup
     Description . . . . . . . . . . . . .  Connect to VMS1X system
     Dead Check Counter  . . . . .  .  . .  100

     F1=Help  F4=Prompt  F9=Retrieve  F12=Cancel

     (C) Copyright Oracle Corporation, 2006
    _______________________________________________________________________________________

The fields in the panel are described as follows:

`Oracle Server Name` defines the name of the Oracle server that is to be accessed by Oracle Access Manager for AS/400.

`Privileged Oracle Id` designates the Oracle user ID that is associated with the defined server name. For additional information, refer to "Oracle Required Privileges".

`Password for Oracle Id` is the password of the `Privileged Oracle Id` that is associated with the defined server name. This parameter will be saved in an encrypted form. For additional information, refer to "Oracle Required Privileges".

`Startup Parameter` specifies when the AM400_RID program can attempt to connect to the designated server. If the `STARTUP` keyword is specified, then the AM400_RID program attempts to connect to the designated server when the AM400_RID program is started. If no keyword is specified, then the AM400_RID program attempts to connect to the designated server when any applications tries to connect to the server for the first time.

`Description` is a description of the defined `oracle_server.`

### 7.5.4 Step 3: Add or Remove AM400_COM Privileged User IDs

The options found on the panel in Example 7-1, "Change Resolve In Doubt (RID) Options Panel" can also be dynamically changed using the AM400_COM program. Use of the AM400_COM program to change these options is restricted to the set of AS/400 user IDs found in the panel in Example 7-3.

The AM400_COM program allows specified AS/400 user profiles to dynamically add or remove Oracle server parameters, to designate AM400_RID connections to Oracle servers, and to shut down connections to those Oracle servers. For example, if an Oracle server is not designated in the "Oracle Server Name" column in the Change RID (Resolve In Doubt) Options panel, then it can be defined dynamically (for the current invocation of the AM400_RID program) with the AM400_COM program. Refer to "AM400_COM Program" for additional information about the AM400_COM program.

To access the Change RID (Resolve In Doubt) Privileged IDs panel, first issue the `CHG2PCPRM` command. The first panel that is displayed confirms which library is to be changed and confirms the four single-valued RID options that can be used. Then select PF10 to display the Change RID (Resolve In Doubt) Privileged IDs panel.

Example 7-3 Change Privileged IDs (Relative to AM400_COM) Panel

    _______________________________________________________________________________________
                          Change Privileged IDs (Relative to AM400_COM)
                                                                           System: HQAS400
     Installation Library:  ORAAM130

     Time options, press Enter.
       1=Add  4=Remove

     Opt       Privileged ID
               QSECOFR
               AM400ADMIN

                                                                           Bottom

     F1=Help  F4=Prompt  F9=Retrieve  F10=Switch to OracleServers screen  F12=Cancel

     (C) Copyright Oracle Corporation, 2006
    _______________________________________________________________________________________

The user ID in the `Privileged ID` field has full access to the AM400_COM program.

The Change Privileged IDs (Relative to AM400_COM) panel is distributed with the default of QSECOFR in the `Privileged ID` field. `QSECOFR` is the AS/400 security administration user ID.

### 7.5.5 Step 4: Start the Subsystem that Runs the AM400_RID Program

To enable two-phase commit processing, the ``subsystem ``that ``runs the AM400_RID program must be started using the following command:

    STRSBS install_lib/install_lib

where `install_lib` is the library in which Access Manager is installed.

### 7.5.6 Step 5: Start the AM400_COM Program (Optional)

You can now start the AM400_COM program to dynamically change the current options for the AM400_RID program. The changes made by the AM400_COM program are not persistent. They are valid only for the current instance of the AM400_RID program. Refer to "AM400_COM Program" for more information about starting the AM400_COM program.

## 7.6 Oracle Required Privileges

For the AM400_RID program to be able to perform resolve in-doubt processing, it must be able to connect to any Oracle server to which typical users connect with STRSQL or with a SQL-program package. The AM400_RID program connects to the Oracle server that is designated by `Oracle Server Name` in the panel in Example 7-2. The AM400_RID program connects to the Oracle server by using the Oracle user ID and password that are specified for that server on the `CHG2PCPRM` panel (Example 7-2, "Change or Enter New Server Attributes Panel"). The privileged user ID must have the following privileges:

- read access to the `SYS.PENDING_TRANS$` table

- update access to the `SYS.PENDING_SESSIONS$` table

- privileges to `COMMIT` or `ROLLBACK` any transaction with the `FORCE` option. For more information about the `FORCE` option, refer to the Oracle Database SQL Reference.

To grant these privileges, the data base administrator (DBA) can issue the following `GRANT` commands in Oracle by using SQL*Plus. This can be done for every server that is designated in the `Oracle Server Name` column in the Change RID (Resolve In Doubt) Options panel (Example 7-1).

    GRANT CREATE SESSIONS, FORCE ANY TRANSACTION TO userid;
    GRANT SELECT ON SYS.PENDING_TRANS$ TO userid;
    GRANT UPDATE ON SYS.PENDING_SESSIONS$ TO userid;

where `userid` is the Oracle user ID for the associated server, as set or changed in the Change or Enter New Server Attributes panel.

## 7.7 AM400_COM Program

The AM400_COM program is used to communicate dynamically with the AM400_RID program. The AM400_RID program controls the automatic resolve in-doubt processing of Oracle Access Manager for AS/400. The AM400_COM program can query the AM400_RID program for the following information:

- the servers to which the AM400_RID program is connected

- the user tasks connected to the various Oracle servers

The AM400_COM program can also be used to dynamically alter the environment of the AM400_RID program during an AS/400 user session. The AM400_COM program can be used to perform the following tasks:

- add Oracle servers to the list of servers to which normal Access Manager tasks can connect

- remove Oracle servers from the list of available servers so that no more connections can be made from AS/400 user tasks to those servers

- change the `DATAQUEUE_TIMEOUT` parameter

- change the `CLEANUP_DELAY` parameter

To access the AM400_COM program, type the following on the AS/400 command line:

    CALL AM400_COM PARM(install_lib)

where `install_lib` is the library in which Access Manager is installed.

### 7.7.1 AM400_COM Commands

The AM400_COM program recognizes the following commands:

`STATUS` requests global connection status of the AM400_RID program. Examples:

- Request the status of all servers to which the AM400_RID program has a connection:

      STATUS SERVERS

  or

      STATUS

- Request the status of the connection between AM400_RID and Oracle server `server_name`:

      STATUS SERVER server_name

- Request the status of what is known about a given job: The `numeric_value` is the six digit AS/400 job number.

      STATUS JOB numeric_value

`ADD` adds a server to the list of available servers. For example:

    ADD SERVER server_name oracle_id oracle_password

`REMOVE` removes a server from the list of available servers. For example:

    REMOVE SERVER server_name

`STARTUP` starts a connection between the AM400_RID program and an Oracle server. For example:

    STARTUP SERVER server_name

`SHUTDOWN` shuts down a connection between the AM400_RID program and an Oracle server. For example:

    SHUTDOWN SERVER server_name [FORCE]

`MODIFY` modifies the Oracle user ID and the password to be used when the AM400_RID program attempts to connect to a specific Oracle server. For example:

    MODIFY SERVER server_name oracle_id oracle_password

`QUERY` queries various items within the AM400_RID environment. Examples:

1. Query whether the AM400_RID program knows about a given server or has a connection to a given server. If the `STARTUP` parameter is given, and if the AM400_RID program knows about the server, but no connection exists from the AM400_RID program to that server, then an attempt is made to start the connection. For example:

       QUERY SERVER server_name [STARTUP]

2. Query the value that is set for the `DATAQUEUE_TIMEOUT` parameter. For example:

       QUERY DATAQUEUE_TIMEOUT

3. Query the value that is set for the `CLEANUP_DELAY` parameter. For example:

       QUERY CLEANUP_DELAY

4. Query the value that is set for the `DEAD_CHECK_COUNTER` parameter. For example:

       QUERY DEAD_CHECK_COUNTER

5. Query the value that is set for the `TRACE_LEVEL` parameter. For example:

       QUERY TRACE_LEVEL

6. Query the value that is set for the `SERVER_STARTUP_TIMEOUT` parameter. This value is used by the AS/400 user task when it is attempting to ensure that the AM400_RID program has a connection to the same Oracle server to which the user task is attempting to connect. If the user task does not get a positive response from the AM400_RID program within the timeout value that is set by `SERVER_STARTUP_TIMEOUT`, then the user task is unable to `CONNECT` to the Oracle server. For example:

       QUERY SERVER_STARTUP_TIMEOUT

`SET` sets various items within the AM400_RID environment. The following items can be set:

    SET CLEANUP_DELAY numeric_value
    SET DATAQ_TIMEOUT numeric_value
    SET DEAD_CHECK_COUNTER numeric_value
    SET TRACE_LEVEL numeric_value
    SET SERVER_STARTUP_TIMEOUT numeric_value

`LIST` displays various items in the AM400_RID environment. Examples:

1. A list of all the servers that are known to the AM400_RID program and whether or not a connection exists from the AM400_RID program to that server. For example:

       LIST AVAILABLE SERVERS

2. A list of various items about the shared data space through which the AM400_COM program, the AM400_RID program, and the AS/400 user tasks communicate. Use of this command might be requested by Oracle Support Services. An example:

       LIST DATASPACE HEADER

3. A list of all used entries in the shared data space. Use of this command might be requested by Oracle Support Services. An example:

       LIST ALL DATASPACE ENTRIES

`FORCE` forces a scan of all AS/400 user jobs that are known to the AM400_RID program and marks any jobs that need to use resolve in-doubt processing. For example:

    FORCE SCAN